using System;
using System.Collections.Generic;
using System.Text;
using DTOLayer;
using System.Data.OleDb;
namespace BUSLayer
{
    public class PhieuRutTienDAO
    {

        public static void InsertPhieuRutTien(PhieuRutTienDTO pr)
        {
            //B1&B2 Tao chuoi ket noi va ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL ket noi CSDL
            string strSQL = "";

            strSQL = "Insert Into PHIEURUTTIEN(MaPhieuRutTien,MaSo,SoTienRut,NgayRut) Values(?,?,?,?)";
            //B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@MaPhieuRutTien", OleDbType.LongVarChar);
            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);
            cmd.Parameters.Add("@SoTienRut", OleDbType.Double);
            cmd.Parameters.Add("@NgayRut", OleDbType.Date);

            cmd.Parameters[0].Value = pr.MaPhieuRut;
            cmd.Parameters[1].Value = pr.MaSo;
            cmd.Parameters[2].Value = pr.SoTienRut;
            cmd.Parameters[3].Value = pr.NgayRut;

            cmd.ExecuteNonQuery();

            //B5Dong ket noi
            cn.Close();
        }
        public static long MaxMaPhieuRutTien()
        {
            //B1&B2 tao chuoi kiet noi
            // OleDbConnection cn;
            // cn = Database.ConnectionData();
            //B3 Tao chuoi ket noi CSDL
            string sql = "SELECT  Max(MaPhieuRutTien) From PHIEURUTTIEN";
            //B4 thuc thi chuoi sql
            // OleDbCommand cmd = new OleDbCommand(sql, cn);
            long kq;
            // kq= long.Parse( cmd.ExecuteScalar().ToString());
            //cn.Close();

            kq = Database.ExecuteScalar(sql);
            return kq;

        }
        public static void UpdatePhieuRutTien(PhieuRutTienDTO pr)
        {

            //B1&B2 Tao chuoi ket noi va mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL thao tac CSDL
            string strSQL;

            strSQL = "UPDATE PHIEURUTTIEN Set MaSo=? ,SoTienRut=?,NgayRut=? Where MaPhieuRutTien=?";


            //B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);
            cmd.Parameters.Add("@SoTienRut", OleDbType.Double);
            cmd.Parameters.Add("@NgayRut", OleDbType.Date);
            cmd.Parameters.Add("MaPhieuRutTien", OleDbType.LongVarChar);


            cmd.Parameters["@MaSo"].Value = pr.MaSo;
            cmd.Parameters["@SoTienRut"].Value = pr.SoTienRut;
            cmd.Parameters["@NgayRut"].Value = pr.NgayRut;
            cmd.Parameters["MaPhieuRutTien"].Value = pr.MaPhieuRut;

            cmd.ExecuteNonQuery();
            //B5 Dong ket noi
            cn.Close();

        }
        public static void DeletePhieuRutTienTheoMaPhieuRut(long mpr)
        {
            //B1 &B2 Tao chuoi ket noi va mo doi tuong ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B2 Tao chuoi strSQL Thao Tac CSDL
            string strSQl = "";
            strSQl = "Delete From PHIEURUTTIEN Where MaPhieuRutTien= ?";
            //B4 Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQl, cn);

            cmd.Parameters.Add("@MaPhieuRutTien", OleDbType.LongVarChar);
            cmd.Parameters["@MaPhieuRutTien"].Value = mpr;
            cmd.ExecuteNonQuery();

            //B5 dong ket noi
            cn.Close();
        }

        public static double TongChi(DateTime ngaybaocao, long maso)
        {
            double kq = 0;

            //B1&B2 tao chuoi kiet noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi ket noi CSDL
            string strSQL = "Select Sum(SoTienRut) From PHIEURUTTIEN Where MaSo=? and NgayRut =?";
            //B4 thuc thi chuoi sql
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);
            cmd.Parameters.Add("@NgayRut", OleDbType.Date);
            cmd.Parameters["@maso"].Value = maso;
            cmd.Parameters["@NgayRut"].Value=ngaybaocao;

            kq = double.Parse(cmd.ExecuteScalar().ToString());
            cn.Close();
            return kq;


        }
        //
        public static List<PhieuRutTienDTO> TinhTongRutTheoMaSoVaNgayThu(DateTime ng)
        {
            List<PhieuRutTienDTO> ls = new List<PhieuRutTienDTO>();
            //B1&B2 Tao chuoi ket noi va mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL thao tac CSDL
            string strSQL = @"SELECT MaPhieuRutTien, MaSo,Sum(SoTienRut) AS TongTienRut,NgayRut
                        FROM PHIEURUTTIEN
                        GROUP BY MaPhieuRutTien, MaSo, NgayRut
                        HAVING NgayRut=#" + ng.ToString() + "#";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            //cmd.Parameters.Add("@NgayRut", OleDbType.Date);
            //cmd.Parameters["@NgayRut"].Value = ng;

            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                PhieuRutTienDTO pr = new PhieuRutTienDTO();
                pr.MaPhieuRut = long.Parse(dr["MaPhieuRutTien"].ToString());
                pr.SoTienRut = double.Parse(dr["TongTienRut"].ToString());
                pr.MaSo = long.Parse(dr["MaSo"].ToString());
                pr.NgayRut = DateTime.Parse(dr["NgayRut"].ToString());


                ls.Add(pr);

            }
            cn.Close();
            return ls;

           

        }

    }
}
